package com.ying.dao.order;

import com.ying.po.order.Order;
import com.ying.po.order.Seller;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;

public interface OrderRepository extends JpaRepository<Order, Integer> {

    @Query(value = "select * from t_order where is_delete=0 and user_id=:userId", nativeQuery = true)
    public Page<Order> getOrderPageIsNotDeleted(Pageable pageable, @Param("userId") Integer userId);

    @Query(value = "select * from t_order where is_delete=0 and user_id=:userId", nativeQuery = true)
    public List<Order> findAllIsNotDelete(@Param("userId")Integer userId);

    @Query(value = "select * from t_order where is_delete=0 and user_id=:userId and is_done=1", nativeQuery = true)
    public List<Order> findAllIsNotDeleteAndIsDone(@Param("userId")Integer userId);


    @Query(value = "select * from t_order where is_delete=0 and id=:id", nativeQuery = true)
    public Optional<Order> findByIdAndIsNotDelete(@Param("id") Integer id);

//    // 1.
    @Query(value = "select * from t_order where is_delete=0 and user_id=:u and buyer_id=:b", nativeQuery = true)
    Page<Order> findByBuyerId(Pageable pageable, @Param("u")Integer u, @Param("b")Integer b);

    @Query(value = "select * from t_order where is_delete=0 and user_id=:u and express_id in :ids", nativeQuery = true)
    Page<Order> finByExpressId(Pageable pageable, @Param("u")Integer u, @Param("ids")List<Integer> ids);

    @Query(value = "select * from t_order where is_delete=0 and user_id=:u and is_done=:d", nativeQuery = true)
    Page<Order> finByStatus(Pageable pageable, @Param("u")Integer u, @Param("d")Integer d);

    @Query(value = "select * from t_order where is_delete=0 and user_id=:u and buyer_id=:b and express_id in :ids", nativeQuery = true)
    Page<Order> findByBuyerIdAndExpressId(Pageable pageable, @Param("u")Integer u, @Param("b")Integer b, @Param("ids")List<Integer> ids);

    @Query(value = "select * from t_order where is_delete=0 and user_id=:u and buyer_id=:b and is_done=:d", nativeQuery = true)
    Page<Order> findByBuyerIdAndStatus(Pageable pageable, @Param("u")Integer u, @Param("b")Integer b, @Param("d")Integer d);

    @Query(value = "select * from t_order where is_delete=0 and user_id=:u and is_done=:d and express_id in :ids", nativeQuery = true)
    Page<Order> findByExpressIdAndStatus(Pageable pageable, @Param("u")Integer u, @Param("ids")List<Integer> ids, @Param("d")Integer d);

    @Query(value = "select * from t_order where is_delete=0 and user_id=:u and is_done=:d and buyer_id=:b and express_id in :ids", nativeQuery = true)
    Page<Order> findByBuyerIdAndExpressIdAndStatus(Pageable pageable, @Param("u") Integer u, @Param("b") Integer b, @Param("ids") List<Integer> ids, @Param("d") Integer d);

    @Query(value = "select price from t_order where user_id=:userId and is_delete=0", nativeQuery = true)
    List<BigDecimal> findByUserId(@Param("userId") Integer userId);

    @Query(value = "select * from t_order where user_id=:userId and is_delete=0 and buyer_id=:i", nativeQuery = true)
    List<Order> findByBuyerIdWithoutPage(@Param("userId") Integer userId, @Param("i")Integer i);

    @Query(value = "select * from t_order where is_delete=0 and user_id=:userId order by create_time asc", nativeQuery = true)
    List<Order> findAllIsNotDeleteOrderByTime(@Param("userId")Integer userId);

    @Query(value = "select * from t_order where is_delete=0 and user_id=:userId and year(create_time)=:year order by create_time asc", nativeQuery = true)
    List<Order> findAllByYearAndOrderByTime(@Param("userId")Integer userId, @Param("year")Integer year);

    @Query(value = "select * from t_order where is_delete=0 and user_id=:userId and buyer_id=:buyerId and year(create_time)<:year", nativeQuery = true)
    List<Order> findLessYear(@Param("userId")Integer userId, @Param("buyerId")Integer buyerId, @Param("year")Integer year);

    @Query(value = "select distinct buyer_id from t_order where is_delete=0 and user_id=:userId and year(create_time)=:year", nativeQuery = true)
    List<Integer> findAllOldBuyer(@Param("userId")Integer userId, @Param("year")Integer year);
}
